# 访问sqlite数据库
import sqlite3
from typing import List, Optional
from config import app_config


class ChatSessionRepository:
    """
    会话表(chat_sessions)的数据访问层
    表结构:
    - id: integer primary key autoincrement
    - session_name: varchar(255) not null (会话名称)
    - character_id: integer not null (关联的角色ID)
    - start_time: datetime default (datetime('now', 'localtime')) (会话开始时间)
    - last_active_time: datetime default (datetime('now', 'localtime')) (最后活跃时间)
    - remark: text (备注)
    - updated_at: datetime default (datetime('now', 'localtime')) (更新时间)
    - deleted: boolean default 0 (软删除标志)
    """

    def __init__(self):
        """初始化数据库连接"""
        self.db_path = app_config.sqlite_db_path

    def _get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
        return conn

    def create_session(self, session_data: dict) -> int:
        """
        创建新的会话记录
        
        Args:
            session_data: 会话数据字典，包含以下字段：
                - session_name: 会话名称
                - character_id: 关联的角色ID
                - remark: 备注 (可选)
            
        Returns:
            int: 新创建记录的ID
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            
            # 从字典中提取字段值
            session_name = session_data.get('session_name')
            character_id = session_data.get('character_id')
            remark = session_data.get('remark')
            
            cursor.execute("""
                INSERT INTO chat_sessions (session_name, character_id, remark, deleted)
                VALUES (?, ?, ?, ?)
            """, (session_name, character_id, remark, 0))
            conn.commit()
            result = cursor.lastrowid or 0
        finally:
            conn.close()
        return result

    def get_session_by_id(self, session_id: int) -> Optional[dict]:
        """
        根据ID获取会话记录
        
        Args:
            session_id: 会话ID
            
        Returns:
            dict: 会话记录或None（如果未找到）
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, session_name, character_id, remark, start_time, last_active_time, updated_at
                FROM chat_sessions
                WHERE id = ? AND deleted = 0
            """, (session_id,))
            row = cursor.fetchone()
            result = dict(row) if row else None
        finally:
            conn.close()
        return result

    def get_all_sessions(self) -> List[dict]:
        """
        获取所有会话记录
        
        Returns:
            List[dict]: 所有会话记录列表
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, session_name, character_id, remark, start_time, last_active_time, updated_at
                FROM chat_sessions
                WHERE deleted = 0
                ORDER BY last_active_time DESC
            """)
            rows = cursor.fetchall()
            result = [dict(row) for row in rows]
        finally:
            conn.close()
        return result

    def update_session(self, session_id: int, **kwargs) -> bool:
        """
        更新会话记录
        
        Args:
            session_id: 会话ID
            **kwargs: 要更新的字段和值
            
        Returns:
            bool: 更新是否成功
        """
        if not kwargs:
            return False

        # 构建动态更新语句
        fields = []
        values = []
        for key, value in kwargs.items():
            # 确保字段名是表中的有效列
            valid_fields = {'session_name', 'character_id', 'remark'}
            if key in valid_fields:
                fields.append(f"{key} = ?")
                values.append(value)

        # 如果没有有效的字段要更新，则返回False
        if not fields:
            return False

        # 添加更新时间和会话ID到值列表
        values.append(session_id)

        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(f"""
                UPDATE chat_sessions
                SET {', '.join(fields)}, updated_at = datetime('now', 'localtime')
                WHERE id = ? 
            """, values)
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def delete_session(self, session_id: int) -> bool:
        """
        软删除会话记录
        
        Args:
            session_id: 会话ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_sessions
                SET deleted = 1, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (session_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def restore_session(self, session_id: int) -> bool:
        """
        恢复软删除的会话记录
        
        Args:
            session_id: 会话ID
            
        Returns:
            bool: 恢复是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_sessions
                SET deleted = 0, updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (session_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def update_last_active_time(self, session_id: int) -> bool:
        """
        更新会话的最后活跃时间
        
        Args:
            session_id: 会话ID
            
        Returns:
            bool: 更新是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE chat_sessions
                SET last_active_time = datetime('now', 'localtime'), updated_at = datetime('now', 'localtime')
                WHERE id = ?
            """, (session_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result

    def hard_delete_session(self, session_id: int) -> bool:
        """
        硬删除会话记录（永久删除）
        
        Args:
            session_id: 会话ID
            
        Returns:
            bool: 删除是否成功
        """
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM chat_sessions WHERE id = ?", (session_id,))
            conn.commit()
            result = cursor.rowcount > 0
        finally:
            conn.close()
        return result


# 创建全局实例
chat_session_repo = ChatSessionRepository()